-- NA INCLUSAO GERA AS ETIQUETAS  E NA EXCLUSAO EXCLUI AS TABELAS AMARRADAS
CREATE OR REPLACE TRIGGER T_ATS_DETOF_BEFORE 
BEFORE INSERT OR DELETE ON ATS_DETOF FOR EACH ROW
DECLARE
v_ofcons NUMBER;
v_etiqueta NUMBER;
v_erro NUMBER(1);
BEGIN
  IF INSERTING THEN
  
    FOR I IN
    (
      SELECT * FROM F_CADNP, ATS_OF
      WHERE CNP_CODIGO = COF_CODINP
      AND   COF_CODIOF = :NEW.DOF_CODIOF
    ) LOOP
       
      FOR X IN 1 .. :NEW.DOF_QTDPRG / NVL(:NEW.DOF_QTDEMB,1) LOOP     
        SELECT SEQ_CODETI.NEXTVAL INTO v_etiqueta FROM DUAL;
      
       INSERT INTO F_ETIQUETAS
       ( ETI_CODETI, ETI_TIPETI, ETI_DATETI, ETI_MODELO, ETI_CODPRO, ETI_LOCEST, 
         ETI_QTDENV, ETI_QTDEMB, ETI_COPIAS, ETI_COMP18/*ORIGEM*/, ETI_COMP19/*COD. ATS_DETOF*/, ETI_COMP20 /*BLOQUEADA*/, 
         ETI_EATIVA, ETI_IMPRES 
       )
       VALUES(v_etiqueta,1,SYSDATE,null ,I.COF_CODIOF, I.CNP_LCACAB,
       :NEW.DOF_QTDPRG, :NEW.DOF_QTDEMB,1,1,:NEW.DOF_CODIGO,1,1,0);
      END LOOP;
    END LOOP; 
  ELSIF DELETING THEN
    IF :OLD.DOF_QTDREA <> 0 OR :OLD.DOF_QTDREF <> 0 THEN
       RAISE_APPLICATION_ERROR(-20001, 'Item possui apontamento e nao pode ser excluido');  
    ELSE
       v_erro := 0;
       BEGIN
         SELECT 1 INTO v_erro FROM ATS_OFCONS
         WHERE OFC_COFDET =:OLD.DOF_CODIGO
         AND   OFC_QTDEMP > 0;
       EXCEPTION WHEN NO_DATA_FOUND THEN
          v_erro:=0;
       END;
       IF v_erro = 1 THEN      
          RAISE_APPLICATION_ERROR(-20001, 'Item possui Materiais reservados, transfira as etiquetas antes de excluir'); 
       ELSE
          FOR X IN ( SELECT * FROM ATS_OFCONS WHERE OFC_COFDET = :NEW.DOF_CODIGO ) LOOP
              DELETE ATS_DETOFCONS
              WHERE DFC_COFCON = X.OFC_CODIGO;
              
              DELETE ATS_OFCONS
              WHERE OFC_CODIGO = X.OFC_CODIGO; 
           END LOOP;
       END IF;
    END IF;
  END IF;
END;